package com.iengoo.inductionms.common;

import com.iengoo.inductionms.entity.Employee;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class StaffInfoExcel {
    public static void export(String path, String name, List<Employee> employees) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);//自定义设置列宽和行高
        sheet.setDefaultRowHeightInPoints(20);
        /**
         * 字体设置
         */
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗

        /**
         * 单元格背景为黄色
         */
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//设置背景色
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle.setAlignment(CellStyle.ALIGN_CENTER );//居中
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle.setFont(font);

        /**
         * 单元格背景为蓝色
         */
        CellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setFillForegroundColor(IndexedColors. INDIGO.getIndex());//设置背景色
        cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle1.setAlignment(CellStyle.ALIGN_CENTER );//居中
        cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle1.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle1.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle1.setFont(font);

        /**
         * 单元格背景为紫色
         */
        CellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setFillForegroundColor(IndexedColors. LAVENDER.getIndex());//设置背景色
        cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle2.setAlignment(CellStyle.ALIGN_CENTER );//居中
        cellStyle2.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle2.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle2.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle2.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle2.setFont(font);

        /**
         * 单元格背景为灰色
         */
        CellStyle cellStyle3 = wb.createCellStyle();
        cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//设置背景色
        cellStyle3.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle3.setAlignment(CellStyle.ALIGN_CENTER );//居中
        cellStyle3.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle3.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle3.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle3.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle3.setFont(font);

        HSSFRow row = sheet.createRow(0);

        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("部门");

        HSSFCell cell1 = row.createCell(1);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue("员工编号");

        HSSFCell cell2 = row.createCell(2);
        cell2.setCellStyle(cellStyle);
        cell2.setCellValue("姓名");

        HSSFCell cell3 = row.createCell(3);
        cell3.setCellStyle(cellStyle);
        cell3.setCellValue("性别");

        HSSFCell cell4 = row.createCell(4);
        cell4.setCellStyle(cellStyle);
        cell4.setCellValue("身份证号码");

        HSSFCell cell5 = row.createCell(5);
        cell5.setCellStyle(cellStyle);
        cell5.setCellValue("出生日期");

        HSSFCell cell6 = row.createCell(6);
        cell6.setCellStyle(cellStyle);
        cell6.setCellValue("参加本单位时间");

        HSSFCell cell7 = row.createCell(7);
        cell7.setCellStyle(cellStyle);
        cell7.setCellValue("员工类型");

        HSSFCell cell8 = row.createCell(8);
        cell8.setCellStyle(cellStyle);
        cell8.setCellValue("用工性质");

        HSSFCell cell9 = row.createCell(9);
        cell9.setCellStyle(cellStyle);
        cell9.setCellValue("来源");

        HSSFCell cell10 = row.createCell(10);
        cell10.setCellStyle(cellStyle);
        cell10.setCellValue("分配状态");

        HSSFCell cell11 = row.createCell(11);
        cell11.setCellStyle(cellStyle);
        cell11.setCellValue("员工类别");

        HSSFCell cell12 = row.createCell(12);
        cell12.setCellStyle(cellStyle);
        cell12.setCellValue("人员组");

        HSSFCell cell13 = row.createCell(13);
        cell13.setCellStyle(cellStyle2);
        cell13.setCellValue("户口类别");

        HSSFCell cell14 = row.createCell(14);
        cell14.setCellStyle(cellStyle3);
        cell14.setCellValue("国籍");

        HSSFCell cell15 = row.createCell(15);
        cell15.setCellStyle(cellStyle2);
        cell15.setCellValue("民族");

        HSSFCell cell16 = row.createCell(16);
        cell16.setCellStyle(cellStyle2);
        cell16.setCellValue("籍贯");

        HSSFCell cell17 = row.createCell(17);
        cell17.setCellStyle(cellStyle2);
        cell17.setCellValue("出生地");

        HSSFCell cell18 = row.createCell(18);
        cell18.setCellStyle(cellStyle2);
        cell18.setCellValue("参加工作时间");

        HSSFCell cell19 = row.createCell(19);
        cell19.setCellStyle(cellStyle2);
        cell19.setCellValue("参加移动时间");

        HSSFCell cell20 = row.createCell(20);
        cell20.setCellStyle(cellStyle2);
        cell20.setCellValue("社会保险号");

        HSSFCell cell21 = row.createCell(21);
        cell21.setCellStyle(cellStyle2);
        cell21.setCellValue("婚姻状况");

        HSSFCell cell22 = row.createCell(22);
        cell22.setCellStyle(cellStyle2);
        cell22.setCellValue("健康状况");

        HSSFCell cell23 = row.createCell(23);
        cell23.setCellStyle(cellStyle2);
        cell23.setCellValue("国家");

        HSSFCell cell24 = row.createCell(24);
        cell24.setCellStyle(cellStyle1);
        cell24.setCellValue("省直辖市");

        HSSFCell cell25 = row.createCell(25);
        cell25.setCellStyle(cellStyle1);
        cell25.setCellValue("市");

        HSSFCell cell26 = row.createCell(26);
        cell26.setCellStyle(cellStyle1);
        cell26.setCellValue("地址行");

        HSSFCell cell27 = row.createCell(27);
        cell27.setCellStyle(cellStyle1);
        cell27.setCellValue("邮编");

        HSSFCell cell28 = row.createCell(28);
        cell28.setCellStyle(cellStyle2);
        cell28.setCellValue("移动电话");

        HSSFCell cell29 = row.createCell(29);
        cell29 .setCellStyle(cellStyle2);
        cell29 .setCellValue("家庭电话");

        HSSFCell cell30 = row.createCell(30);
        cell30.setCellStyle(cellStyle2);
        cell30.setCellValue("办公电话");

        HSSFCell cell31 = row.createCell(31);
        cell31.setCellStyle(cellStyle2);
        cell31.setCellValue("email");

        HSSFCell cell32 = row.createCell(32);
        cell32.setCellStyle(cellStyle1);
        cell32.setCellValue("政治面貌");

        HSSFCell cell33 = row.createCell(33);
        cell33.setCellStyle(cellStyle3);
        cell33.setCellValue("加入时间");

        HSSFCell cell34 = row.createCell(34);
        cell34 .setCellStyle(cellStyle1);
        cell34 .setCellValue("学历");

        HSSFCell cell35 = row.createCell(35);
        cell35.setCellStyle(cellStyle1);
        cell35.setCellValue("毕业情况");

        HSSFCell cell36 = row.createCell(36);
        cell36.setCellStyle(cellStyle1);
        cell36.setCellValue("最高学位标识");

        HSSFCell cell37 = row.createCell(37);
        cell37.setCellStyle(cellStyle2);
        cell37.setCellValue("参加工作时学历标识");

        HSSFCell cell38 = row.createCell(38);
        cell38.setCellStyle(cellStyle2);
        cell38.setCellValue("学位类别");

        HSSFCell cell39 = row.createCell(39);
        cell39.setCellStyle(cellStyle2);
        cell39.setCellValue("学位");

        HSSFCell cell40 = row.createCell(40);
        cell40.setCellStyle(cellStyle2);
        cell40.setCellValue("学位授予时间");

        HSSFCell cell41 = row.createCell(41);
        cell41.setCellStyle(cellStyle2);
        cell41.setCellValue("入学时间");

        HSSFCell cell42 = row.createCell(42);
        cell42.setCellStyle(cellStyle2);
        cell42.setCellValue("毕肄业时间");

        HSSFCell cell43 = row.createCell(43);
        cell43.setCellStyle(cellStyle2);
        cell43.setCellValue("学制");

        HSSFCell cell44 = row.createCell(44);
        cell44.setCellStyle(cellStyle2);
        cell44.setCellValue("毕业学校");

        HSSFCell cell45 = row.createCell(45);
        cell45.setCellStyle(cellStyle2);
        cell45.setCellValue("所学专业");

        HSSFCell cell46 = row.createCell(46);
        cell46.setCellStyle(cellStyle2);
        cell46.setCellValue("学习形式");

        HSSFCell cell47 = row.createCell(47);
        cell47.setCellStyle(cellStyle2);
        cell47.setCellValue("所学专业分类");

        HSSFCell cell48 = row.createCell(48);
        cell48.setCellStyle(cellStyle2);
        cell48.setCellValue("是否全职");

        HSSFCell cell49 = row.createCell(49);
        cell49.setCellStyle(cellStyle1);
        cell49.setCellValue("职位编码");

        HSSFCell cell50 = row.createCell(50);
        cell50.setCellStyle(cellStyle1);
        cell50.setCellValue("职位名称");

        HSSFCell cell51 = row.createCell(51);
        cell51.setCellStyle(cellStyle3);
        cell51.setCellValue("职等");

        HSSFCell cell52 = row.createCell(52);
        cell52.setCellStyle(cellStyle1);
        cell52.setCellValue("工资单");

        HSSFCell cell53 = row.createCell(53);
        cell53.setCellStyle(cellStyle1);
        cell53.setCellValue("雇主");

        HSSFCell cell54 = row.createCell(54);
        cell54.setCellStyle(cellStyle1);
        cell54.setCellValue("纳税地区");

        HSSFCell cell55 = row.createCell(55);
        cell55 .setCellStyle(cellStyle1);
        cell55 .setCellValue("社会保险/PHF摊缴地区");

        createRows(sheet, employees);

        FileOutputStream fileOut = new FileOutputStream(path + name + ".xls");
        wb.write(fileOut);
        fileOut.close();

    }

    public static void createRows(Sheet sheet, List<Employee> employees) {
        for (int i = 0; i < employees.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Employee e = employees.get(i);

            Cell cell = row.createCell(0);
            cell.setCellValue(nullToBlank(e.getDepartment()));

            Cell cell1 = row.createCell(1);
            cell1.setCellValue(nullToBlank(e.getEmployeeId()));

            Cell cell2 = row.createCell(2);
            cell2.setCellValue(nullToBlank(e.getEmployeeName()));

            Cell cell3 = row.createCell(3);
            cell3.setCellValue(nullToBlank(e.getEmployeeGender()));

            Cell cell4 = row.createCell(4);
            cell4.setCellValue(nullToBlank(e.getIdcardNo()));

            Cell cell5 = row.createCell(5);
            cell5.setCellValue(format(e.getBirthDate()));

            Cell cell6 = row.createCell(6);
            cell6.setCellValue(format(e.getEntryTime()));

            Cell cell7 = row.createCell(7);
            cell7.setCellValue(nullToBlank(e.getEmployeeType()));

            Cell cell8 = row.createCell(8);
            cell8.setCellValue(nullToBlank(e.getEmploymentNature()));

            Cell cell9 = row.createCell(9);
            cell9.setCellValue(nullToBlank(e.getEmployeeSource()));

            Cell cell10 = row.createCell(10);
            cell10.setCellValue(nullToBlank(e.getDistribytionState()));

            Cell cell11 = row.createCell(11);
            cell11.setCellValue(nullToBlank(e.getDegreeCategory()));

            Cell cell12 = row.createCell(12);
            cell12.setCellValue(nullToBlank(e.getPersonnelGroup()));

            Cell cell13 = row.createCell(13);
            cell13.setCellValue(nullToBlank(e.getAccountType()));

            Cell cell14 = row.createCell(14);
            cell14.setCellValue(nullToBlank(e.getNationality()));

            Cell cell15 = row.createCell(15);
            cell15.setCellValue(nullToBlank(e.getNation()));

            Cell cell16 = row.createCell(16);
            cell16.setCellValue(nullToBlank(e.getNativePlace()));

            Cell cell17 = row.createCell(17);
            cell17.setCellValue(nullToBlank(e.getBirthPlace()));

            Cell cell18 = row.createCell(18);
            cell18.setCellValue(format(e.getWorkTime()));

            Cell cell19 = row.createCell(19);
            cell19.setCellValue(format(e.getJoinMobileTime()));

            Cell cell20 = row.createCell(20);
            cell20.setCellValue(nullToBlank(e.getSsn()));

            Cell cell21 = row.createCell(21);
            cell21.setCellValue(nullToBlank(e.getMaritalStatus()));

            Cell cell22 = row.createCell(22);
            cell22.setCellValue(nullToBlank(e.getHealthCondition()));

            Cell cell23 = row.createCell(23);
            cell23.setCellValue(nullToBlank(e.getCountry()));

            Cell cell24 = row.createCell(24);
            cell24.setCellValue(nullToBlank(e.getProvince()));

            Cell cell25 = row.createCell(25);
            cell25.setCellValue(nullToBlank(e.getCity()));

            Cell cell26 = row.createCell(26);
            cell26.setCellValue(nullToBlank(e.getAddress()));

            Cell cell27 = row.createCell(27);
            cell27.setCellValue(nullToBlank(e.getPostcodes()));

            Cell cell28 = row.createCell(28);
            cell28.setCellValue(nullToBlank(e.getMobilePhone()));

            Cell cell29 = row.createCell(29);
            cell29.setCellValue(nullToBlank(e.getHomePhone()));

            Cell cell30 = row.createCell(30);
            cell30.setCellValue(nullToBlank(e.getOfficePhone()));

            Cell cell31 = row.createCell(31);
            cell31.setCellValue(nullToBlank(e.getEmail()));

            Cell cell32 = row.createCell(32);
            cell32.setCellValue(nullToBlank(e.getPoliticalStatus()));

            Cell cell33 = row.createCell(33);
            cell33.setCellValue(format(e.getJoinTime()));

            Cell cell34 = row.createCell(34);
            cell34.setCellValue(nullToBlank(e.getEducation()));

            Cell cell35 = row.createCell(35);
            cell35.setCellValue(nullToBlank(e.getGraduationSituation()));

            Cell cell36 = row.createCell(36);
            cell36.setCellValue(nullToBlank(e.getHighestDegree()));

            Cell cell37 = row.createCell(37);
            cell37.setCellValue(nullToBlank(e.getWorkEducation()));

            Cell cell38 = row.createCell(38);
            cell38.setCellValue(nullToBlank(e.getDegreeCategory()));

            Cell cell39 = row.createCell(39);
            cell39.setCellValue(nullToBlank(e.getAcademicDegree()));

            Cell cell40 = row.createCell(40);
            cell40.setCellValue(format(e.getDegreeAwardTime()));

            Cell cell41 = row.createCell(41);
            cell41.setCellValue(format(e.getAdmissionTime()));

            Cell cell42 = row.createCell(42);
            cell42.setCellValue(format(e.getGraduationTime()));

            Cell cell43 = row.createCell(43);
            cell43.setCellValue(nullToBlank(e.getEducationalSystem()));

            Cell cell44 = row.createCell(44);
            cell44.setCellValue(nullToBlank(e.getGraduationSchool()));

            Cell cell45 = row.createCell(45);
            cell45.setCellValue(nullToBlank(e.getProfession()));

            Cell cell46 = row.createCell(46);
            cell46.setCellValue(nullToBlank(e.getLearningModality()));

            Cell cell47 = row.createCell(47);
            cell47.setCellValue(nullToBlank(e.getProfessionalClassification()));

            Cell cell48 = row.createCell(48);
            cell48.setCellValue(nullToBlank(e.getFullTime()));

            Cell cell49 = row.createCell(49);
            cell49.setCellValue(nullToBlank(e.getJobCode()));

            Cell cell50 = row.createCell(50);
            cell50.setCellValue(nullToBlank(e.getJobName()));

            Cell cell51 = row.createCell(51);
            cell51.setCellValue(nullToBlank(e.getJobRank()));

            Cell cell52 = row.createCell(52);
            cell52.setCellValue(nullToBlank(e.getPayroll()));

            Cell cell53 = row.createCell(53);
            cell53.setCellValue(nullToBlank(e.getEmployer()));

            Cell cell54 = row.createCell(54);
            cell54.setCellValue(nullToBlank(e.getRatepayingPlace()));

            Cell cell55 = row.createCell(55);
            cell55.setCellValue(nullToBlank(e.getSocialSecurityPlace()));

        }
    }
    public static String nullToBlank(String o) {
        return o == null ? "" : o;
    }

    public static String format(Date date) {
        return date == null ? "" : new SimpleDateFormat("yyyy-MM-dd").format(date);
    }
}
